<?php

namespace App\Libs\wrapper;

use App\Models\BaseModel;
use Illuminate\Support\Facades\DB;
use phpDocumentor\Reflection\Types\Collection;

class VirtualPost extends Comm
{
    const WEEKTYPE = 1;
    const MONTHTYPE = 2;
    private static $nowTime;
    private static $nowDate;
    private static $adminIds = [1, 14, 81];

    /**
     * 获取列表
     *
     * @param $data
     * @return array
     */
    public static function getIndex($data)
    {
        $page = isset($data['page']) ? $data['page'] : 1;
        $pageSize = isset($data['page_size']) ? $data['page_size'] : 10;
        $start_date = request()->post('start_time', '');
        $end_date = request()->post('end_time', '');
        $year = request()->post('year', '');
        $month = request()->post('month', '');
        $week = request()->post('week', '');
        $post_id = request()->post('post_id', '');
        if ($week) list($start_date, $end_date) = self::weekToTime($week, $month, $year);
//        $total = request()->post('is_total', 0);
        $uid = self::getUser()->Id;
        $is_admin = self::isAdmin($uid);

        $res = DB::table('vp_matter_list as vml')
            ->select(
                ['vml.id', 'u.Id as user_id', 'u.account', 'p.name as post_name', 'vml.matter_name', 'vml.content', 'vml.deal_date', 'vmd.update_date', 'vml.deal_time',
                    DB::raw('( CASE WHEN ISNULL(vmd.update_date) AND deal_date < CURDATE() = TRUE THEN 0
                                    WHEN vmd.status = 1 THEN 1
                                    WHEN vmd.status = 2 THEN 2 
                                    END ) AS is_OnTime'),
                    'vmd.score', 'vmd.comments', 'vmd.results', DB::raw('IFNULL(vmd.file,\'\') as file'), DB::raw('IFNULL(vmd.img,\'\') as img'),
                    'vml.is_auto',
                    DB::raw('( CASE WHEN ISNULL(vmd.snapshot) THEN 0
                                    ELSE 1
                                    END ) AS is_snapshot'),
                ])
            ->leftJoin('users as u', 'u.id', '=', 'vml.uid')
            ->leftJoin('vp_post as p', 'p.id', '=', 'vml.post_id')
            ->leftJoin('vp_matter_detail as vmd', 'vml.id', '=', 'vmd.list_id')
            ->where(['p.is_del' => 0])
            ->where(function ($query) use ($start_date, $end_date) {
                if (!empty($start_date) && !empty($end_date))
                    return $query->whereBetween('deal_date', [$start_date, $end_date]);
            })
            ->where(function ($query) use ($data) {
                if (isset($data['username']) && !empty($data['username']))
                    return $query->where('u.id', '=', $data['username']);
            })
            ->where(function ($query) use ($post_id) {
                if ($post_id && $post_id !== '-1') return $query->where('vml.post_id', '=', $post_id);
            })
            ->where(function ($query) use ($month, $week, $year) {
                if (!empty($month) && !empty($year)) {
                    $month = date('Ym', strtotime($year . '-' . $month));
                    return $query->where('vml.ym', '=', $month);
                }
            })
            ->where(function ($query) use ($data) {
                if (isset($data['matter_name']) && $data['matter_name'] !== '-1')
                    return $query->where('vml.matter_id', '=', $data['matter_name']);
            })
            // 列表查询人员查看权限卡控
//            ->where(function ($query) use ($is_admin, $uid) {
//                if (!$is_admin) return $query->where('u.id', '=', $uid);
//            })
            ->where(function ($query) use ($data) {
                if (isset($data['is_anshi']) && $data['is_anshi'] !== '' && $data['is_anshi'] != -1)
                    return $query->where(['vmd.status' => $data['is_anshi']]);
//                    return $query->where(Db::raw('( CASE WHEN ISNULL(vmd.update_date) AND deal_date < CURDATE() = TRUE THEN 0
//                                    WHEN vmd.update_date < vml.deal_date
//                                    AND ISNULL(vmd.file) = FALSE THEN
//                                        1
//                                    WHEN vmd.update_date > vml.deal_date
//                                    AND ISNULL(vmd.file) = FALSE THEN
//                                        2
//                                    END )'), $data['is_anshi']);
            })
//            ->toRawSql();
            ->forPage($page, $pageSize)
            ->paginate($pageSize);
//        print_r($res);exit();

        $count = $res->total();
        //将laravel对象转为数组
        $res = array_map('get_object_vars', $res->items());
        $weekAry = array("日", "一", "二", "三", "四", "五", "六");
        foreach ($res as &$v) {
            $v['deal_date'] = $v['deal_date'] . '(周' . $weekAry[date("w", $v['deal_time'])] . ')';
        }
        $total = [
            'all' => 0,
            'anshi' => 0,
            'noDone' => 0,
            'done' => 0,
            'completionRate' => 0,
            'avgScore' => 0,
        ];
        if ($res) {
            $collection = collect($res);
            $total['all'] = $collection->count();
            $total['anshi'] = $collection->where('is_OnTime', '=', 1)->count();
            $total['noDone'] = $collection->where('update_date', '=', NULL)->count();
            $total['done'] = $collection->where('update_date', '!=', NULL)->count();
            $total['completionRate'] = round($total['done'] / $total['all'], 2);
            $total['avgScore'] = round($collection->where('score', '>', 0)->avg('score'), 2);
        }

        $is_admin = self::isAdmin($uid);
        return [$count, $res, $total, $is_admin];
    }

    /**
     * 获取搜索列表
     *
     * @return \string[][]
     */
    public static function getSearchList()
    {
        $post_id = request()->post('post_id');
        $user_id = self::getUser()->Id;
        $is_admin = self::isAdmin($user_id);
        $post_list = DB::table('vp_post')
            ->where(['is_del' => 0])
//            ->where(function ($query) use ($is_admin, $post_id, $user_id) {
//                if (!$is_admin) {
//                    return $query->select(['id', 'name'])->whereRaw("find_in_set({$user_id},ids)");
//                }
//            })
            ->get();
        $matter_list = DB::table('vp_matter as m')
            ->leftJoin('vp_post as vp', 'm.post_id', '=', 'vp.id')
            ->where(function ($query) use ($post_id) {
                if ($post_id) return $query->where(['m.post_id' => $post_id]);
            })
            ->where(function ($query) use ($is_admin, $user_id) {
                if (!$is_admin) return $query->select(['id', 'name'])->whereRaw("find_in_set({$user_id},ids)");
            })
            ->select(['m.id', 'm.matter_name'])
            ->where(['m.is_del' => 0, 'vp.is_del' => 0])
            ->groupBy('m.id')
            ->get()->toArrayList();
        if (!$post_id) {
            $matter_list = [['id' => '-1', 'matter_name' => '全部'], ['id' => 0, 'matter_name' => '临时事项']];
        } else {
            $matter_list[] = ['id' => '-1', 'matter_name' => '全部'];
            $matter_list[] = ['id' => 0, 'matter_name' => '临时事项'];
            $matter_list = array_merge(collect($matter_list)->sortBy('id')->toArray());

        }
        $post_list[] = ['id' => '-1', 'name' => '全部'];
        $list = [
            'post_list' => array_merge(collect($post_list)->sortBy('id')->toArray()),
            'matter_name' => $matter_list,
            'is_anshi' => [['id' => '-1', 'name' => '全部'], ['id' => 0, 'name' => '否'], ['id' => 1, 'name' => '是']],
        ];
        return $list;
    }

    /**
     * 是否是管理员
     *
     * @param $user_id
     * @return bool
     */
    public static function isAdmin($user_id)
    {
        if (in_array($user_id, self::$adminIds)) {
            return true;
        }
        return false;
    }

    /**
     * 创建临时事项
     *
     * @return bool
     */
    public static function createMatter()
    {
        try {
            $params = request()->all();
//        $user_id = self::getUser()->Id;
//        $post = Db::table('vp_post')->whereRaw("find_in_set({$user_id},`ids`)")->first();

            $matterName = request()->post('matter_name', '');
            $post_id = request()->post('post_id');
            db::beginTransaction();
            if (!isset($params['user_ids']) || empty($params['user_ids'])){
                $user_id = self::getUser()->Id;
                $insertRes = DB::table('vp_matter_list')->insert(
                    [
                        'post_id' => $post_id,
                        'matter_id' => 0,
                        'matter_name' => $matterName,
                        'uid' => $user_id,
                        'type' => 0,//临时事项固定type=0
                        'deal_time' => strtotime($params['deal_date']),
                        'deal_date' => $params['deal_date'],
                        'ym' => date('Ym', strtotime($params['deal_date'])),
                        'add_time' => time(),
                    ]
                );
                if (!$insertRes) {
                    throw new \Exception('临时事项保存出错！出错人：'.$user_id);
                }
            }else{
                foreach ($params['user_ids'] as $user_id){
                    $insertRes = DB::table('vp_matter_list')->insert(
                        [
                            'post_id' => $post_id,
                            'matter_id' => 0,
                            'matter_name' => $matterName,
                            'uid' => $user_id,
                            'type' => 0,//临时事项固定type=0
                            'deal_time' => strtotime($params['deal_date']),
                            'deal_date' => $params['deal_date'],
                            'ym' => date('Ym', strtotime($params['deal_date'])),
                            'add_time' => time(),
                        ]
                    );
                    if (!$insertRes) {
                        throw new \Exception('临时事项保存出错！出错人：'.$user_id);
                    }
                }
            }

            db::commit();
            return ['code' => 200, 'msg' => '保存成功！'];
        }catch (\Exception $e){
            return ['code' => 500, 'msg' => '新增异常，原因：'.$e->getMessage().'；位置：'.$e->getLine()];
        }

    }

    public static function postList()
    {
//        $res = DB::table('vp_post as vp')
//            ->select(['vp.*', 'vp.id as post_id', Db::raw('GROUP_CONCAT(`vm`.`matter_name`) as matters'), DB::raw('(SELECT GROUP_CONCAT(account) from users WHERE Id in (vp.ids) group By state) as users')])
//            ->leftJoin('vp_matter as vm', 'vm.post_id', '=', 'vp.id AND vm.is_del=0')
//            ->where(['vp.is_del' => 0, 'vm.is_del' => 0])
//            ->toRawSql();


        $res = Db::select("SELECT
                        `vp`.*, `vp`.`id` AS `post_id`,`vm`.`content`,
                        GROUP_CONCAT(`vm`.`matter_name`) AS matters
                    FROM
                        `vp_post` AS `vp`
                    LEFT JOIN `vp_matter` AS `vm` ON `vm`.`post_id` = `vp`.`id` AND vm.is_del=0
                    WHERE
                        (
                            `vp`.`is_del` = 0
                        
                        )
                    GROUP BY `vp`.`id`");

        $baseModel = new BaseModel();
        foreach ($res as $k=>$v){
            $idArr = explode(',',$v->ids);
            $res[$k]->users = '';
            foreach ($idArr as $d){
                $userdata = $baseModel->GetUsers($d);
                if($userdata){
                    $res[$k]->users .= $userdata['account'].',';
                }
            }
            rtrim($res[$k]->users, ',');
        }

        return $res;
    }

    /**
     * 新增岗位
     *
     * @return bool
     * @throws \Exception
     */
    public static function insertPost()
    {
        $user_id = self::getUser()->Id;
        $name = request()->post('post_name');
        $ids = request()->post('post_ids', '');
        $matters = request()->post('matters', '');
        if (!$name) throw new \Exception('事项名不能为空');
        Db::beginTransaction();
        $id = DB::table('vp_post')->insertGetId(['name' => $name, 'ids' => $ids ?: '']);
        $addRes = 1;
        if ($matters) {
            $insertData = [];
            foreach ($matters as $k => $v) {
                $insertData[] = ['post_id' => $id, 'matter_name' => $v['matter_name'], 'type' => $v['type'], 'date' => $v['date'], 'sort' => 0, 'is_open' => 0, 'add_time' => time(), 'update_time' => time(), 'update_user' => $user_id, 'content' => $v['content' ?? '']];
            }
            if (!$insertData) throw new \Exception('matters不能为空');
            $addRes = DB::table('vp_matter')->insert($insertData);
        }
        if ($id && $addRes) {
            Db::commit();
            return true;
        }
        DB::rollBack();
        return false;
    }

    /**
     * 编辑岗位
     *
     * @return bool
     * @throws \Exception
     */
    public static function updatePost()
    {
        $id = request()->post('post_id');
        $name = request()->post('post_name');
        $ids = request()->post('post_ids');
        $matters = request()->post('matters', '');
        $user_id = self::getUser()->Id;
        if (!$name) throw new \Exception('事项名不能为空');
        if (!$id) throw new \Exception('post_id不能为空');
        if (!$ids) throw new \Exception('post_ids不能为空');
        Db::beginTransaction();
        $updateRes = 1;
        $addRes = 1;
        $insertData = [];
        $res = DB::table('vp_post')->where(['id' => $id])->update(['name' => $name, 'ids' => $ids, 'update_time' => time(), 'update_user' => self::getUser()->Id]);
        DB::table('vp_matter')->where(['post_id' => $id])->update(['is_del' => 1, 'update_time' => time(), 'update_user' => self::getUser()->Id]);
        foreach ($matters as $k => $v) {
            if (isset($matters['matter_id']) && !empty($matters['matter_id'])) {
                $updateRes = DB::table('vp_post')->where(['id' => $matters['matter_id']])->update(['post_id' => $id, 'matter_name' => $v['matter_name'], 'type' => $v['type'], 'date' => $v['date'], 'sort' => 0, 'is_open' => 0, 'is_del' => 1, 'add_time' => time(), 'update_time' => time(), 'update_user' => $user_id]);
            } else {
                $insertData[] = ['post_id' => $id, 'matter_name' => $v['matter_name'], 'type' => $v['type'], 'date' => $v['date'], 'sort' => 0, 'is_open' => 0, 'add_time' => time(), 'update_time' => time(), 'update_user' => $user_id, 'content' => $v['content'] ?? ''];
            }
            if (!$updateRes) break;
        }
        if ($insertData) $addRes = DB::table('vp_matter')->insert($insertData);
        //新增，修改岗位将当月余下事项列表删除，重构事项列表
        $findRes = DB::table('vp_matter as vm')
            ->select(['vm.matter_name', 'vm.type', 'vm.date', 'vp.ids', 'vp.id as post_id', 'vm.id as matter_id','vm.is_auto', 'vm.content'])
            ->leftJoin('vp_post as vp', 'vp.id', '=', 'vm.post_id')
            ->where(['vp.is_del' => 0, 'vm.is_del' => 0, 'vp.id' => $id])
            ->where('vm.id', '>', 0)
            ->get()
            ->toArrayList();

        $delRes = DB::table('vp_matter_list')->where(['post_id' => $id, ['deal_time', '>', time()]])->delete();

        $createRes = self::getDateRes($findRes, 1);

        if ($res && $updateRes && $addRes && $delRes !== false && $createRes) {
            Db::commit();
            return true;
        }
        Db::rollBack();
        return false;
    }

    /**
     * 删除岗位
     *
     * @return bool
     * @throws \Exception
     */
    public static function delPost()
    {
        $id = request()->post('post_id');
        if (!$id) throw new \Exception('post_id不能为空');
        $res = DB::table('vp_post')->where(['id' => $id])->update(['is_del' => 1, 'update_time' => time(), 'update_user' => self::getUser()->Id]);
        if ($res) return true;
        return false;
    }

    /**
     * 设置事项(管理员设置)
     *
     * @return bool
     */
    public static function setMatter()
    {
        $params = request()->all();
        $matter_id = request()->post('matter_id', '');
        $user_id = self::getUser()->Id;
        $saveData = [
            'matter_name' => $params['matter_name'],
            'type' => $params['type'],
            'date' => $params['date'],
            'sort' => $params['sort'],
            'add_time' => time(),
            'update_time' => time(),
            'update_user' => $user_id,
        ];
        if ($matter_id) {
            unset($saveData['add_time']);
            $saveRes = DB::table('vp_matter')->where(['id' => $matter_id])->update($saveData);
        } else {
            $saveRes = DB::table('vp_matter')->insert($saveData);
        }
        if ($saveRes) return true;
        return false;
    }

//    /**
//     *
//     * @param $matterIds
//     * @param $post_id
//     * @return bool
//     * @throws \Exception
//     */
//    public static function bindMatter($matterIds, $post_id)
//    {
//        $user_id = self::getUser()->Id;
//        $matterAry = explode(',', $matterIds);
//        $delRes = true;
//        $findRes = Db::table('vp_post_matter')->where(['post_id' => $post_id])->first();
//        foreach ($matterAry as $v) {
//            $insertData[] = ['post_id' => $post_id, 'matter_id' => $v, 'update_time' => time(), 'update_user' => $user_id];
//        }
//        Db::beginTransaction();
//        if ($findRes) {
//            $delRes = Db::table('vp_post_matter')->where(['post_id' => $post_id])->delete();
//        }
//        $insertRes = Db::table('vp_post_matter')->insert($insertData);
//        if ($delRes && $insertRes) {
//            Db::commit();
//            return true;
//        }
//        Db::rollBack();
//        return false;
//    }

    /**
     * 设置下个月事项
     *
     * @param $data
     * @param int $is_update
     * @return bool
     * @throws \Exception
     */
    public static function getDateRes($data, $is_update = 0)
    {
        self::$nowTime = time();
        self::$nowDate = date('Ymd', time());
        $newData = [];
        //剥离ids里的ids，重组insert数组
        foreach ($data as $k => $v) {
            $ids = explode(',', $v['ids']);
            foreach ($ids as $val) {
                $data[$k]['ids'] = $val;
                $newData[] = $data[$k];
            }
        }
        foreach ($newData as $nk => $nv) {
            $dateAry[] = self::getDate($nv, $is_update);
        }
        $collection = collect($dateAry);
        $insertData = $collection->collapse()->toArray();
        $res = DB::table('vp_matter_list')->insert($insertData);
        if ($res !== false) {
            return true;
        }
        return false;
    }

    /**
     * 获取matter_list的insert数据
     *
     * @param $data
     * @param int $is_update
     * @return mixed
     * @throws \Exception
     */
    public static function getDate($data, $is_update = 0)
    {
        $nowTime = request()->post('month', date('Y-m-d', self::$nowTime));
        if ($is_update) {
            $firstDay = date("Y-m-01", strtotime("$nowTime"));
            $endDay = date("Y-m-d", strtotime("$firstDay + 1 month -1 day"));
            $firstDay = $nowTime;
        } else {
            $firstDay = date("Y-m-01", strtotime("$nowTime"));
            $endDay = date("Y-m-d", strtotime("$firstDay + 1 month -1 day"));
        }
        switch ($data['type']) {
            case self::WEEKTYPE:
                $result = self::getWeeklyByDate($firstDay, $endDay, $data['date']);
                break;
            case self::MONTHTYPE:
                $result = self::getNextMonth($data['date'], $nowTime, $is_update);
                break;
            default:
                throw new \Exception('数组类型不存在，请联系管理员');
        }
        if (!$result) throw new \Exception('获取数据失败，请联系管理员');
        foreach ($result as $k => $v) {
            $deal_time = strtotime($v);
            if ($data['is_auto'] == 1) {
                $list[] = ['is_auto' => 1, 'uid' => $data['ids'], 'post_id' => $data['post_id'], 'matter_id' => $data['matter_id'], 'type' => $data['type'], 'matter_name' => $data['matter_name'], 'deal_date' => $v, 'deal_time' => $deal_time, 'Ym' => date('Ym', $deal_time), 'add_time' => self::$nowTime, 'content' => $data['content'] ?? ''];
            } else {
                $list[] = ['is_auto' => 0, 'uid' => $data['ids'], 'post_id' => $data['post_id'], 'matter_id' => $data['matter_id'], 'type' => $data['type'], 'matter_name' => $data['matter_name'], 'deal_date' => $v, 'deal_time' => $deal_time, 'Ym' => date('Ym', $deal_time), 'add_time' => self::$nowTime, 'content' => $data['content'] ?? ''];
            }
        }

        return $list;
    }

    /**
     * 获取周数据
     *
     * @param string $start
     * @param string $end
     * @param int $weekDay
     * @return array
     */
    public static function getWeeklyByDate($start = '2022-04-05', $end = '2022-04-30', $weekDay = 1)
    {
        $WORK_DAY = [
            1 => ['en' => 'Monday', 'cn' => '一'],
            2 => ['en' => 'Tuesday', 'cn' => '二'],
            3 => ['en' => 'Wednesday', 'cn' => '三'],
            4 => ['en' => 'Thursday', 'cn' => '四'],
            5 => ['en' => 'Friday', 'cn' => '五']
        ];
        //获取每周要执行的日期 例如: 2016-01-02
        $start = empty($start) ? date('Y-m-d') : $start;
        $startTime = strtotime($start);

        $startDay = date('N', $startTime);
        if ($startDay < $weekDay) {
            $startTime = strtotime($WORK_DAY[$weekDay]['en'], strtotime($start)); //本周x开始, 例如, 今天(周二)用户设置每周四执行, 那本周四就会开始执行
        } else {
            $startTime = strtotime('next ' . $WORK_DAY[$weekDay]['en'], strtotime($start));//下一个周x开始, 今天(周二)用户设置每周一执行, 那应该是下周一开始执行
        }
        $endTime = strtotime($end);

        $list = [];
        for ($i = 0; ; $i++) {
            $dayOfWeek = strtotime("+{$i} week", $startTime); //每周x

            if ($dayOfWeek > $endTime) {
                break;
            }
            $list[] = date('Y-m-d', $dayOfWeek);
        }
        return $list;
    }

    /**
     * 获取月数据
     *
     * @param $date
     * @param $nowDate
     * @param int $is_update
     * @return array
     */
    public static function getNextMonth($date, $nowDate, $is_update = 0)
    {
        if (!$nowDate) $nowDate = self::$nowDate;
        if ($is_update) {
            if ($date == -1) {
                $firstDate = date('Y-m-1', time());
                $MonthDate = date("Y-m-d", strtotime("$firstDate +1 month -1 day"));
            } else {
                $MonthDate = date("Y-m-$date", strtotime("$nowDate"));
            }
        } else {
            if ($date == -1) {
                $firstDate = date('Y-m-1', time());
                $MonthDate = date("Y-m-d", strtotime("$firstDate +1 month -1 day"));
            } else {
                $MonthDate = date("Y-m-$date", strtotime("$nowDate"));
            }
        }

        return [$MonthDate];
    }

    /**
     * 将
     *
     * @param $week
     * @param $month
     * @param $year
     */
    public static function weekToTime($week, $month, $year)
    {
        switch ($week) {
            case 1:
                $start_time = date("{$year}-{$month}-1");
                $end_time = date("{$year}-{$month}-7");
                break;
            case 2:
                $start_time = date("{$year}-{$month}-8");
                $end_time = date("{$year}-{$month}-14");
                break;
            case 3:
                $start_time = date("{$year}-{$month}-15");
                $end_time = date("{$year}-{$month}-22");
                break;
            case 4:
                $start_time = date("{$year}-{$month}-23");
                $end_time = date("{$year}-{$month}-d", strtotime($year . '-' . $month . '+1 month -1 day'));
                break;
            default :
                throw new \Exception('只选择1-4周');
        }
        return [$start_time, $end_time];
    }

    /**
     * 获取用户
     *
     * @return object
     */
    public static function getUser()
    {
        return DB::table('users')->where(['token' => request()->post('token')])->first();
    }


    /**
     * 将process_name转义成英文
     *
     * @return string[]
     */
    public static function processNameAry()
    {
//        面布,里布,辅料,产前样,产前会,上线,查前期,首扎成品,中查,下线,后整,尾查,出货
        return [
            //卖单
//            '下合同' => 'contract',
//            '色卡寄出' => 'color_card_mail',
//            '色卡意见' => 'color_card_opinion',
//            '面料到仓' => 'fabric_arrive',
            '辅料到仓' => 'accessories_arrive',
//            '产前样' => 'sample',
//            '产前样意见' => 'sample_opinion',
//            '开裁' => 'start_clipping',
//            '上线' => 'start_online',
//            '后整' => 'behind_arrangement',
//            '验货' => 'inspection',
            //
//            头样意见,品质布,下合同,色卡寄出,色卡意见,面料到仓,产前样,产前样意见,产前会议,开裁,上线,后整,验货,出货
            '头样寄出' => 'first_sample_sending',
            '头样意见' => 'first_sample_opinion',
            '品质布' => 'quality_cloth',
            '下合同' => 'contract',
            '色卡寄出' => 'color_card_mail',
            '色卡意见' => 'color_card_opinion',
            '面料到仓' => 'fabric_arrive',
            '产前样' => 'sample',
            '产前样意见' => 'sample_opinion',
            '产前会议' => 'pp_meeting',
            '开裁' => 'start_clipping',
            '上线' => 'start_online',
            '后整' => 'behind_arrangement',
            '验货' => 'inspection',
            //外贸
            '面布' => 'face_cloth',
            '里布' => 'lining_cloth',
            '辅料' => 'accessories',
            '产前会' => 'sample_meet',
            '查前期' => 'check_prophase',
            '中查' => 'mid_search',
            '首扎成品' => 'first_product',
            '下线' => 'dead_line',
            '尾查' => 'final_search',
            '出货' => 'shipment',
        ];
    }

    /**
     *
     * @param $type
     * @return \string[][]
     */
    private static function getTitle($type)
    {
        if ($type == '卖单') {
//            $title = [
//                'billno' => '合同号',
//                'input_date' => '接单日期',
//                'supplier_short_name' => '加工厂',
//                'goods_no' => '款号',
//                'img' => '图片',
//                'cnname' => '品类',
//                'sku' => 'Sku',
//                'num' => '下单数量',
//                'contract_plan_date' => '计划合同时间',
//                'color_card_mail_plan_date' => '计划色卡寄出时间',
//                'color_card_opinion_plan_date' => '计划色意见',
//                'fabric_arrive_plan_date' => '计划面料到仓时间',
//                'accessories_arrive_plan_date' => '计划辅料倒仓时间',
//                'sample_plan_date' => '计划产前样时间',
//                'sample_opinion_plan_date' => '计划产前样意见时间',
//                'start_clipping_plan_date' => '计划开裁时间',
//                'start_online_plan_date' => '计划上线时间',
//                'behind_arrangement_plan_date' => '计划后整时间',
//                'inspection_plan_date' => '计划验货时间',
//                'shipment_plan_date' => '计划出货时间',
//            ];
//
//            $title2 = [
//                'billno' => '合同号',
//                'input_date' => '接单日期',
//                'supplier_short_name' => '加工厂',
//                'goods_no' => '款号',
//                'img' => '图片',
//                'cnname' => '品类',
//                'sku' => 'Sku',
//                'num' => '下单数量',
//                'contract_complete_date' => '实际合同时间',
//                'color_card_mail_complete_date' => '实际色卡寄出时间',
//                'color_card_opinion_complete_date' => '实际色意见',
//                'fabric_arrive_complete_date' => '实际面料到仓时间',
//                'accessories_arrive_complete_date' => '实际辅料倒仓时间',
//                'sample_complete_date' => ' 实际产前样时间',
//                'sample_opinion_complete_date' => '实际产前样意见时间',
//                'start_clipping_complete_date' => '实际开裁时间',
//                'start_online_complete_date' => '实际上线时间',
//                'behind_arrangement_complete_date' => '实际后整时间',
//                'inspection_complete_date' => '实际验货时间',
//                'shipment_complete_date' => '实际出货时间',
//            ];


            $title = [
                'billno' => '合同号',
                'input_date' => '接单日期',
                'supplier_short_name' => '加工厂',
                'goods_no' => '款号',
                'img' => '图片',
                'cnname' => '品类',
                'sku' => 'Sku',
                'num' => '下单数量',
                'closed_status' => '是否结案',
                'first_sample_sending_plan_date' => '计划头样寄出时间',
                'first_sample_opinion_plan_date' => '计划头样意见时间',
                'quality_cloth_plan_date' => '计划品质布时间',
                'contract_plan_date' => '计划下合同时间',
                'color_card_mail_plan_date' => '计划色卡寄出时间',
                'color_card_opinion_plan_date' => '计划色卡意见时间',
                'fabric_arrive_plan_date' => '计划面料到仓时间',
                'sample_plan_date' => '计划产前样时间',
                'sample_opinion_plan_date' => '计划产前样意见时间',
                'pp_meeting_plan_date' => '计划产前会议时间',
                'start_clipping_plan_date' => '计划开裁时间',
                'start_online_plan_date' => '计划上线时间',
                'behind_arrangement_plan_date' => '计划后整时间',
                'inspection_plan_date' => '计划验货时间',
            ];

            $title2 = [
                'billno' => '合同号',
                'input_date' => '接单日期',
                'supplier_short_name' => '加工厂',
                'goods_no' => '款号',
                'img' => '图片',
                'cnname' => '品类',
                'sku' => 'Sku',
                'num' => '下单数量',
                'closed_status' => '是否结案',
                'first_sample_sending_complete_date' => '实际头样寄出时间',
                'first_sample_opinion_complete_date' => '实际头样意见时间',
                'quality_cloth_complete_date' => '实际品质布时间',
                'contract_complete_date' => '实际下合同时间',
                'color_card_mail_complete_date' => '实际色卡寄出时间',
                'color_card_opinion_complete_date' => '实际色卡意见时间',
                'fabric_arrive_complete_date' => '实际面料到仓时间',
                'sample_complete_date' => '实际产前样时间',
                'sample_opinion_complete_date' => '实际产前样意见时间',
                'pp_meeting_complete_date' => '实际产前会议时间',
                'start_clipping_complete_date' => '实际开裁时间',
                'start_online_complete_date' => '实际上线时间',
                'behind_arrangement_complete_date' => '实际后整时间',
                'inspection_complete_date' => '实际验货时间',
            ];
        } else if ($type == '外发') {
            $title = [
                'billno' => '合同号',
                'input_date' => '接单日期',
                'supplier_short_name' => '加工厂',
                'goods_no' => '款号',
                'img' => '图片',
                'cnname' => '品类',
                'sku' => 'Sku',
                'num' => '下单数量',
                'closed_status' => '是否结案',
                'face_cloth_plan_date' => '计划面布时间',
                'lining_cloth_plan_date' => '计划里布寄出时间',
                'accessories_plan_date' => '计划辅料时间',
                'sample_meet_plan_date' => '计划产前样时间',
                'start_online_plan_date' => '计划上线时间',
                'check_prophase_plan_date' => '计划查前期时间',
                'first_product_plan_date' => '计划首扎成品时间',
                'mid_search_plan_date' => '计划中查时间',
                'dead_line_plan_date' => '计划下线时间',
                'behind_arrangement_plan_date' => '计划后整时间',
                'final_search_plan_date' => '计划尾查时间',
                'shipment_plan_date' => '计划出货时间',
            ];

            $title2 = [
                'billno' => '合同号',
                'input_date' => '接单日期',
                'supplier_short_name' => '加工厂',
                'goods_no' => '款号',
                'img' => '图片',
                'cnname' => '品类',
                'sku' => 'Sku',
                'num' => '下单数量',
                'closed_status' => '是否结案',
                'face_cloth_complete_date' => '实际面布时间',
                'lining_cloth_complete_date' => '实际里布寄出时间',
                'accessories_complete_date' => '实际辅料时间',
                'sample_meet_complete_date' => '实际产前样时间',
                'start_online_complete_date' => '实际上线时间',
                'check_prophase_complete_date' => '实际查前期时间',
                'first_product_complete_date' => '实际首扎成品时间',
                'mid_search_complete_date' => '实际中查时间',
                'dead_line_complete_date' => '实际下线时间',
                'behind_arrangement_complete_date' => '实际后整时间',
                'final_search_complete_date' => '实际尾查时间',
                'shipment_complete_date' => '实际出货时间',
            ];
        }

        return [$title, $title2];
    }

    /**
     * 负责人名单
     *
     * @return string[]
     */
    public static function personLiable($type)
    {
        if ($type == '外发') {
            return ['张美华', '张美华', '张美华', '张美华', '张美华', '张美华', '张美华', '张美华', '张美华', '曾利敏', '曾利敏', '曾利敏', '周卫强', '周卫强', '周卫强', '周卫强', '周卫强', '周卫强', '周卫强', '周卫强', '张美华'];
        } else {
            return [];
        }
    }

    public static function setMatterExcel($list, $type, $deal_date)
    {
        error_reporting(E_ALL);
        $objPHPExcel = new \PHPExcel();
        $PHPExcel_Style_Alignment = new\PHPExcel_Style_Alignment;
        foreach ($list as $k => $v) {
            $newList[] = $v;
            $newList[] = $v;
        }
        $is_export = request()->get('is_export', 0);
        if (!$is_export) {
            if (!isset($is_export)) {
                return false;
            }
            return $newList;
        }
        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ");
        // 设置excel标题
        list($title, $title2) = self::getTitle($type);

        $objPHPExcel->getActiveSheet()->mergeCells('A1:T1');
        $objPHPExcel->setActiveSheetIndex(0)//Excel的第A列，uid是你查出数组的键值，下面以此类推
        ->setCellValue('A1', '外发加工单进度跟踪总表(实际进度和计划相符用绿色填充,延后用黄色填充,红色为未完成）');
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);    //第一行行高
        $index = 0;
        foreach ($title as $v) {
            $objPHPExcel->getActiveSheet()->getStyle('A2:AU2')->getFont()->setBold(true);//第一行是否加粗
            $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25);    //第一行行高
            $objPHPExcel->getActiveSheet()->setCellValue($cellName[$index] . "2", $v);
            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(60);
            $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
            $index++;
        }
        $index = 0;
        $personList = self::personLiable($type);
        if ($personList) {
            foreach ($personList as $v) {
                $objPHPExcel->getActiveSheet()->setCellValue($cellName[$index] . "3", $v);
                $index++;
            }
        }
        $objPHPExcel->getActiveSheet()->getStyle('A3:AU3')->getFont()->setBold(true);//第一行是否加粗
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(25);    //第一行行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(60);
        $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:AY3')
            ->getAlignment()
            ->setHorizontal($PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置垂直居中
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:AY3')
            ->getAlignment()
            ->setVertical($PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $wenjian = 'assess/Qiniu/newImg/';
//        $wenjian = 'assess/Qiniu';
        if (!is_dir($wenjian)) {
            mkdir($wenjian, 0777, true);
        }

        foreach ($newList as $k => $v) {
            $num = $k + 4;

            //字体大小
            $objPHPExcel->getActiveSheet()
                ->getStyle('A' . $num . ':' . 'AY' . $num)
                ->getFont()
                ->setSize(10);
            //设置水平居中
            $objPHPExcel->getActiveSheet()
                ->getStyle('A' . $num . ':' . 'AY' . $num)
                ->getAlignment()
                ->setHorizontal($PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //设置垂直居中
            $objPHPExcel->getActiveSheet()
                ->getStyle('A' . $num . ':' . 'AY' . $num)
                ->getAlignment()
                ->setVertical($PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //自动换行
            $objPHPExcel->getActiveSheet()
                ->getStyle('A' . $num . ':' . 'AY' . $num)
                ->getAlignment()
                ->setWrapText(true);


            // 重置列索引起始位
            $checkAry = [];
            $i = 0;
            if ($num % 2 == 0) {//偶数
                foreach ($title as $key => $value) {
                    if ($key == 'img' && $v[$key] != '') {
                        $i++;
                    } else {
                        $objPHPExcel->setActiveSheetIndex(0)//Excel的第A列，uid是你查出数组的键值，下面以此类推
                        ->setCellValue($cellName[$i] . $num, $v[$key]);
                        $checkAry = [$cellName[$i] . $num => $v[$key]];
                        $i++;
                    }
                }
            } else {//奇数
                foreach ($title2 as $key => $value) {
                    if ($key == 'img' && $v[$key] != '') {
                        $filename = time() . '_' . (rand(100, 999) * rand(1, 999));
                        $file_n = $_SERVER['DOCUMENT_ROOT'] . '/assess/Qiniu/newImg/' . $filename . '.jpg';
                        file_put_contents($file_n, base64_decode($v[$key]));
                        $img_del[] = $file_n;//用于删除文件
                        //// 图片插入到Excel
                        //获取图片
                        $objDrawing = new \PHPExcel_Worksheet_Drawing();
                        $objDrawing->setPath($file_n);//图片路径
                        // 设置图片宽度高度
                        $objDrawing->setResizeProportional(false);
                        $objDrawing->setHeight(60);//照片高度
                        $objDrawing->setWidth(60); //照片宽度
                        //设置图片要插入的单元格
                        $objDrawing->setOffsetX(20);
                        $objDrawing->setOffsetY(-20);
                        $objDrawing->setCoordinates($cellName[$i] . $num);
                        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
                        $mergeNum = $num - 1;
                        $objPHPExcel->getActiveSheet()->mergeCells("{$cellName[$i]}{$mergeNum}:{$cellName[$i]}{$num}");
                        $i++;
                    } else {
                        if (isset($title[$key])) {
                            $mergeNum = $num - 1;
                            unset($checkAry["{$cellName[$i]}{$mergeNum}"]);
                            $objPHPExcel->getActiveSheet()->mergeCells("{$cellName[$i]}{$mergeNum}:{$cellName[$i]}{$num}");

                            if ($key == 'closed_status' && $v[$key] == '未结案') {
                                $objPHPExcel->setActiveSheetIndex(0)->getCell($cellName[$i] . ($num - 1))->getStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('F56C6C');
                                $objPHPExcel->setActiveSheetIndex(0)->getCell($cellName[$i] . ($num - 1))->getStyle()->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                                $objPHPExcel->setActiveSheetIndex(0)->getCell($cellName[$i] . ($num - 1))->getStyle()->getFont()->getColor()->setRGB('FFFFFF');
                                $objPHPExcel->setActiveSheetIndex(0)->getCell($cellName[$i] . $num)->getStyle()->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                            }
                        } else {
                            $objPHPExcel->setActiveSheetIndex(0)//Excel的第A列，uid是你查出数组的键值，下面以此类推
                            ->setCellValue($cellName[$i] . $num, $v[$key]);
                            $cellInfo = $objPHPExcel->setActiveSheetIndex(0);

                            if ($v[$key . '_is_ontime'] === '0') {//不需要合并单元格的字段必定是时间节点，必定有对应的颜色判断
                                $cellInfo->getCell($cellName[$i] . $num)->getStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('F56C6C');
                                $cellInfo->getCell($cellName[$i] . $num)->getStyle()->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                                $cellInfo->getCell($cellName[$i] . $num)->getStyle()->getFont()->getColor()->setRGB('FFFFFF');
                                $cellInfo->getCell($cellName[$i] . ($num - 1))->getStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('F56C6C');
                                $cellInfo->getCell($cellName[$i] . ($num - 1))->getStyle()->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                                $cellInfo->getCell($cellName[$i] . ($num - 1))->getStyle()->getFont()->getColor()->setRGB('FFFFFF');
                            } elseif ($v[$key . '_is_ontime'] == 1) {
                                $cellInfo->getCell($cellName[$i] . $num)->getStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('C5E0B4');
                                $cellInfo->getCell($cellName[$i] . $num)->getStyle()->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                                $cellInfo->getCell($cellName[$i] . ($num - 1))->getStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('C5E0B4');
                                $cellInfo->getCell($cellName[$i] . ($num - 1))->getStyle()->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                            } elseif ($v[$key . '_is_ontime'] == 2) {
                                $cellInfo->getCell($cellName[$i] . $num)->getStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('FFC000');
                                $cellInfo->getCell($cellName[$i] . $num)->getStyle()->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                                $cellInfo->getCell($cellName[$i] . ($num - 1))->getStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('FFC000');
                                $cellInfo->getCell($cellName[$i] . ($num - 1))->getStyle()->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                            }
                        }
                        $i++;
                    }

                }
            }
        }
//exit();

        $objPHPExcel->getActiveSheet()->setTitle('work');
        $objPHPExcel->setActiveSheetIndex(0);

        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition:attachment;filename={$type}时效({$deal_date}).xls");
        header('Cache-Control: max-age=1');
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter->save('php://output');

        //删除从七牛云下载的文件
        if (!empty($img_del)) {
            foreach ($img_del as $val) {
                unlink($val);
            }
        }

        exit();
    }

    /**
     * 检测是否存在需要自动捕获的事项
     *
     * @throws \Exception
     */
    public static function checkAutoMatter()
    {
        $nowData = request()->get('nowDate', date('Y-m-d', time()));
        $listRes = Db::table('vp_matter_list')->where(['deal_date' => $nowData, 'is_auto' => 1])->get()->toArrayList();
        $insertData = [];
        if ($listRes) {
            $idsAry = [];
            foreach ($listRes as $k => $v) {
                if (self::getSnapshotData($v['matter_name'], $nowData)) {
                    $insertData[] = ['list_id' => $v['id'], 'update_date' => date('Y-m-d', time()), 'update_time' => time(), 'status' => 1, 'snapshot' => self::getSnapshotData($v['matter_name'], $nowData)];
                }
//                $list = ['list_id' => $v['id'], 'snapshot' => self::getSnapshotData($v['matter_name'], $nowData)];
//                self::setMatterSnapshot($list,$v['matter_name']);
                $idsAry[] = $v['id'];
            }
            if ($insertData) {
                Db::beginTransaction();
                $insertRes = Db::table('vp_matter_detail')->insert($insertData);
                $changeRes = Db::table('vp_matter_list')->whereIn('id', $idsAry)->update(['is_auto' => 2]);
                if ($insertRes && $changeRes) {
                    DB::commit();
                    echo '执行成功';
                    exit();
                } else {
                    DB::rollBack();
                }
            }
        }
        echo '无自动执行的数据';
        exit();
    }

    /**
     * 获取快照数据
     *
     * @param $merchandiser_type
     * @param $nowDate
     */
    public static function getSnapshotData($merchandiser_type, $nowDate)
    {
        $end_date = date('Y-m-d 23:59:59', strtotime($nowDate));
        $start_date = date('Y-m-d H:i:s', strtotime("$nowDate -6 day"));
        $sql = "SELECT
            purcontract_no as billno,goods_no,supplier_short_name,0 as sku,num,input_date,cnname,image_file,
            GROUP_CONCAT(process_name ORDER BY sort_index) AS process_name,
            GROUP_CONCAT(plan_date ORDER BY sort_index) AS plan_date,
            GROUP_CONCAT(CASE WHEN complete_date='0000-00-00 00:00:00' or ISNULL(complete_date) THEN '' ELSE complete_date END ORDER BY sort_index) AS complete_date,
           	GROUP_CONCAT(( CASE WHEN (complete_date = '0000-00-00 00:00:00' OR ISNULL(complete_date)) AND plan_date < CURDATE() = TRUE THEN 0
                                    WHEN complete_date <= plan_date THEN 1 WHEN complete_date > plan_date THEN 2 ELSE '' END ) ORDER BY sort_index) AS is_ontime,
            closed_status FROM anok_merchandiser where (`merchandiser_type` = '{$merchandiser_type}' and `closed_date` between '{$start_date}' and '{$end_date}') or (`merchandiser_type` = '{$merchandiser_type}' and closed_status='未结案') GROUP BY billno,goods_no
             ORDER BY closed_status";
        $list = DB::select($sql);
        $list = Collect($list)->toArrayList();
        if (!$list) return false;
        $processNameAry = self::processNameAry();
        foreach ($list as $k => $v) {
            $v['process_name'] = explode(',', $v['process_name']);
            $v['plan_date'] = explode(',', $v['plan_date']);
            $v['complete_date'] = explode(',', $v['complete_date']);
            $v['is_ontime'] = explode(',', $v['is_ontime']);
            if (count($v['process_name']) !== count($v['plan_date'])) throw new \Exception('节点名和计划日期不一致');
            for ($i = 0; $i < count($v['process_name']); $i++) {
//                $list[$k][$v['process_name'][$i].'_plan_date'] = $v['plan_date'][$i];
                if (isset($v['plan_date'][$i]) && !empty($v['plan_date'][$i])) {
                    $v[$processNameAry[$v['process_name'][$i]] . '_plan_date'] = date('Y-m-d', strtotime($v['plan_date'][$i]));
                    $v[$processNameAry[$v['process_name'][$i]] . '_plan_date_is_ontime'] = $v['is_ontime'][$i];
                } else {
                    $v[$processNameAry[$v['process_name'][$i]] . '_plan_date'] = '';
                    $v[$processNameAry[$v['process_name'][$i]] . '_plan_date_is_ontime'] = '';
                }
            }
            for ($i = 0; $i < count($v['process_name']); $i++) {
//                $list[$k][$v['process_name'][$i].'_complete_date'] = $v['plan_date'][$i];
                if (isset($v['complete_date'][$i]) && !empty($v['complete_date'][$i])) {
                    $v[$processNameAry[$v['process_name'][$i]] . '_complete_date'] = date('Y-m-d', strtotime($v['complete_date'][$i]));
                } else {
                    $v[$processNameAry[$v['process_name'][$i]] . '_complete_date'] = '';
                }
                $v[$processNameAry[$v['process_name'][$i]] . '_complete_date_is_ontime'] = $v[$processNameAry[$v['process_name'][$i]] . '_plan_date_is_ontime'];
            }
            $v['img'] = $v['image_file'];
            unset($v['process_name']);
            unset($v['plan_date']);
            unset($v['complete_date']);
            unset($v['is_ontime']);
            unset($v['image_file']);
            $newList[] = $v;
        }
        return json_encode($newList);
    }
}
